package win.larryzeal.logdemo.dao;

import java.sql.*;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;
import win.larryzeal.logdemo.common.BaseDao;
import win.larryzeal.logdemo.pojo.User;

@Repository
public class UserDao extends BaseDao {
	Logger logger = LoggerFactory.getLogger(UserDao.class);

	private class UserRowMapper implements RowMapper<User> {

		@Override
		public User mapRow(ResultSet rs, int rowNum) throws SQLException{
			User user = new User();
			user.setId(Integer.valueOf(rs.getString("id")));
			user.setName(rs.getString("name"));
			String age = rs.getString("age");
			if(age != null && !age.isEmpty()){
				user.setAge(Integer.valueOf(age));
			}
			user.setSex(rs.getString("sex"));
			return user;
		}

	}

	public User getUserById(Integer id){
		String sql = "select * from users where id=?";
		logger.info("---id = " + id);
		return getJdbcTemplate().queryForObject(sql, new UserRowMapper(), id);
//		return super.getJdbcTemplate().queryForObject(sql, new Object[] { id }, new UserRowMapper());
	}

	public List<User> getAllUser(){
		String sql = "select * from users";
		return super.getJdbcTemplate().query(sql, new UserRowMapper());
	}

	public List<User> getUserListByPage(User user, Integer page, Integer size){
		String sql = "select * from users limit ?,?";
		return super.getJdbcTemplate().query(sql, new Object[]{(page - 1) * size, size}, new UserRowMapper());
	}

	public void saveUser(User user){
		String sql = "insert into users (name,sex,age) values(?,?,?)";
		super.getJdbcTemplate().update(sql, user.getName(), user.getSex(), user.getAge());


//		String sql = "insert into users (name, sex, age) values(?, ?, ?)";
//		PreparedStatementCreatorFactory factory = new PreparedStatementCreatorFactory(sql);
//
//		factory.setGeneratedKeysColumnNames("id");
//		factory.setReturnGeneratedKeys(true);
//
//		PreparedStatementCreator psc = factory
//				.newPreparedStatementCreator(new Object[] { user.getName(), user.getSex(), user.getAge() });
//
//		KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
//
//		super.getJdbcTemplate().update(psc, generatedKeyHolder);
//		// get key
//		Number key = generatedKeyHolder.getKey();
//		user.setId(key.intValue());
	}

	public void saveUser2(User user){
		String sql = "insert into users (name, sex, age) values(?, ?, ?)";

		KeyHolder keyHolder = new GeneratedKeyHolder();
		super.getJdbcTemplate().update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException{
				PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, user.getName());
				ps.setString(2, user.getSex());
				ps.setInt(3,user.getAge());
				return ps;
			}
		}, keyHolder);
		user.setId(keyHolder.getKey().intValue());
	}

	public void updateUser(User user){
		String sql = "update users set name=?,sex=?,age=? where id=?";
		super.getJdbcTemplate().update(sql, user.getName(), user.getSex(), user.getAge(), user.getId());
	}

	public void deleteUser(User user){
		String sql = "delete from users where id=?";
		super.getJdbcTemplate().update(sql, user.getId());
	}

}
